package com.insigma.yanwenlong.bookkeeping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.insigma.yanwenlong.bookkeeping.db.jdbc.DBConnection;
import com.insigma.yanwenlong.bookkeeping.pojo.Category;
import com.insigma.yanwenlong.bookkeeping.pojo.vo.CategoryVo;

public class CategoryDaoImpl implements CategoryDao{

	@Override
	public List<Category> findCategories() throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "select * from category";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		List<Category> categories = new ArrayList<Category>();
		while(resultSet.next()) {
			Category category = new Category();
			category.setId(resultSet.getInt("id"));
			category.setCategoryName(resultSet.getString("categoryName"));
			category.setDirectionId(resultSet.getInt("directionId"));
			
			categories.add(category);
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(statement);
		DBConnection.closeConnection(connection);
		
		return categories;
	}

	@Override
	public Category findDirectionIdById(int id) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "select *  from category where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		Category category = new Category();
		if(resultSet.next()) {
			category.setId(resultSet.getInt("id"));
			category.setCategoryName(resultSet.getString("categoryName"));
			category.setDirectionId(resultSet.getInt("directionId"));
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return category;
	}

	@Override
	public List<CategoryVo> findCategoryVos() throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "select c.*,d.directionName from category c inner join direction d on c.directionId = d.id order by id";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		List<CategoryVo> categoryVos = new ArrayList<CategoryVo>();
		while(resultSet.next()) {
			CategoryVo categoryVo = new CategoryVo();
			categoryVo.setId(resultSet.getInt("id"));
			categoryVo.setCategoryName(resultSet.getString("categoryName"));
			categoryVo.setDirectionName(resultSet.getString("directionName"));
			
			categoryVos.add(categoryVo);
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(statement);
		DBConnection.closeConnection(connection);
		
		return categoryVos;
	}

	@Override
	public int addCategory(Category category) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "insert into category (categoryName,directionId) values(?,?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, category.getCategoryName());
		preparedStatement.setInt(2, category.getDirectionId());
		int rowAffected = preparedStatement.executeUpdate();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return rowAffected;
	}
}
